﻿create PROCEDURE Sync.SP_LoanSocialResearch_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @LoanSocialResearch_Temp TABLE 
		(
			LoanSocialResearchID UniqueIdentifier,
			[LoanID] uniqueidentifier,
				[SocialResearchDate] datetime,
				[FamilyNumbers] int,
				[IncomeAmount] float,
				[Notes] nvarchar(MAX),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @LoanSocialResearch_Temp 
	   SELECT	Tbl.Col.value('LoanSocialResearchID[1]','uniqueidentifier') LoanSocialResearchID,
                Tbl.Col.value('LoanID[1]', 'uniqueidentifier') [LoanID],
Tbl.Col.value('SocialResearchDate[1]', 'datetime') [SocialResearchDate],
Tbl.Col.value('FamilyNumbers[1]', 'int') [FamilyNumbers],
Tbl.Col.value('IncomeAmount[1]', 'float') [IncomeAmount],
Tbl.Col.value('Notes[1]', 'nvarchar(MAX)') [Notes],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/LoanSocialResearch') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[LoanSocialResearch] ([LoanSocialResearchID],[LoanID],[SocialResearchDate],[FamilyNumbers],[IncomeAmount],[Notes])
	SELECT t.[LoanSocialResearchID],t.[LoanID],t.[SocialResearchDate],t.[FamilyNumbers],t.[IncomeAmount],t.[Notes]
	FROM  @LoanSocialResearch_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.LoanSocialResearch_Tracking s
        WHERE t.LoanSocialResearchID = s.LoanSocialResearchID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[LoanSocialResearch]
	SET [LoanSocialResearch].[LoanID] = t.[LoanID],[LoanSocialResearch].[SocialResearchDate] = t.[SocialResearchDate],[LoanSocialResearch].[FamilyNumbers] = t.[FamilyNumbers],[LoanSocialResearch].[IncomeAmount] = t.[IncomeAmount],[LoanSocialResearch].[Notes] = t.[Notes] 
	FROM [dbo].[LoanSocialResearch] s JOIN @LoanSocialResearch_Temp t 
    ON t.LoanSocialResearchID = s.LoanSocialResearchID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.LoanSocialResearch_Tracking r
				  WHERE t.LoanSocialResearchID = r.LoanSocialResearchID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[LoanSocialResearch]
    FROM @LoanSocialResearch_Temp t JOIN [dbo].[LoanSocialResearch] s
    ON t.LoanSocialResearchID = s.LoanSocialResearchID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END














